if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EfyLib_PermissionGroupGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EfyLib_PermissionGroupGetAll]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EfyLib_StaffPermissionUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EfyLib_StaffPermissionUpdate]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EfyLib_StaffPermissionGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EfyLib_StaffPermissionGetAll]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EfyLib_UnitInsuranceStaffPermissionUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EfyLib_UnitInsuranceStaffPermissionUpdate]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EfyLib_UnitInsuranceStaffPermissionGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EfyLib_UnitInsuranceStaffPermissionGetAll]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EfyLib_UnitInsuranceGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].EfyLib_UnitInsuranceGetAll
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EfyLib_UnitInsuranceGetAllForGenerateXmlData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].EfyLib_UnitInsuranceGetAllForGenerateXmlData
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EfyLib_PermissiondDistrictGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].EfyLib_PermissiondDistrictGetAll
GO

--------------------------------------------------------------------------------------------------------------- 
--Nguoi tao: HUNGVM
--Ngay tao:  18/09/2009
--Y nghia:   Tra ve danh sach cac quyen
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.EfyLib_PermissionGroupGetAll
	@p_code						nvarchar(100) = 'DM_ NHOM_QUYEN'
	,@p_xml_tag_in_db			Nvarchar(100)
WITH ENCRYPTION
AS
Declare @v_str_sql nvarchar(500),@list_id varchar(50)
	SET NOCOUNT ON
	Set @v_str_sql = ' Select a.C_CODE, a.C_NAME, ' + 'dbo.f_GetValueOfXMLtag(C_XML_DATA, ' + char(39) + @p_xml_tag_in_db + char(39) + ' ) as XML_TAG_IN_DB '
	Set @v_str_sql = @v_str_sql + ' From  T_EFYLIB_LIST a, T_EFYLIB_LISTTYPE b '
	Set @v_str_sql = @v_str_sql + ' Where a.FK_LISTTYPE = b.PK_LISTTYPE '
	If @p_code Is Not Null And @p_code <>''
		Begin
			Set @v_str_sql = @v_str_sql + ' And  b.C_CODE = ' + char(39) + @p_code + char(39)
		End
	Set @v_str_sql = @v_str_sql + ' And a.C_STATUS =  '+char(39) + 'HOAT_DONG' + char(39)
	--Order by C_ORDER
	Set @v_str_sql = @v_str_sql + ' Order By a.C_ORDER '
	Exec (@v_str_sql)
	SET NOCOUNT OFF
Return 0
Go
/*
Exec dbo.EfyLib_PermissionGetAll
	 @p_code = 'DM_NHOM_QUYEN'
	,@p_xml_tag_in_db = 'quyen_thuocnhom'
*/
---------------------------------------------------------------------------------------------------
--Nguoi tao: Vu Manh Hung
--Ngay tao: 20/09/2009
--Chuc nang: Cap nhat quyen cho NSD
CREATE PROCEDURE dbo.EfyLib_StaffPermissionUpdate
	@p_permission_id	Varchar(50) 						-- Khoa
	,@p_staff_id_list	Nvarchar(max) = ''					-- Danh sach ID NSD
	,@p_permission_list Nvarchar(max) = ''					-- Danh sach quyen
	,@psDelimitor		Varchar(10)							-- Ky tu phan tach giua cac phan tu
WITH ENCRYPTION --Ma hoa SP
AS
	Declare @Index Int, @count Int, @countStaff Int, @p_new_id Uniqueidentifier, @staffId Varchar(50)
	SET XACT_ABORT ON
	BEGIN TRANSACTION
	--Ban giao ho so (Insert new row in T_QLDT_PROCESS)
	Set @count = dbo.f_ListLen(@p_staff_id_list,@psDelimitor)	
		Set @Index = 1
		While @Index <= @count
		Begin
			-- Id staff
			Set @staffId = dbo.f_ListGetAt(@p_staff_id_list,@Index,@psDelimitor)
			--Kiem tra ID Staff @staffId da ton tai chua
			Select @countStaff = Count(*) From T_EFYLIB_PERMISSTION Where FK_STAFF = @staffId
			--Da phan quyen
			If @countStaff > 0
				Begin
					Update T_EFYLIB_PERMISSTION Set C_PERMISSION_LIST = @p_permission_list From T_EFYLIB_PERMISSTION Where FK_STAFF = @staffId
				End
			Else
				Begin
					Set @p_new_id = newid()
					Insert Into T_EFYLIB_PERMISSTION (PK_PERMISSION,FK_STAFF,C_PERMISSION_LIST) Values (@p_new_id,@staffId,@p_permission_list)
				End
			Set @Index = @Index + 1
		End			
	COMMIT TRANSACTION 	
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec dbo.Doc_StaffPermissionUpdate
	@p_permission_id	= ''
	,@p_staff_id_list	= '123!~~!2!~~!33!~~!44!~~!55'
	,@p_permission_list = 'a!~~!b!~~!c!~~!d!~~!e'
	,@psDelimitor		= '!~~!'

Select * From T_DOC_PERMISSION
*/
-------------------------------------------------------------------------------------------------------------------------------------------------
--Nguoi tao: HUNGVM
--Ngay tao:  20/09/2009
--Y nghia:   Tra ve danh sach cac quyen cua mot hoac nhieu NSD
-------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.EfyLib_StaffPermissionGetAll
	@p_staff_id_list	Nvarchar(Max) = ''					-- Danh sach ID NSD
	,@psDelimitor		Varchar(10)							-- Ky tu phan tach giua cac phan tu
WITH ENCRYPTION
AS
Declare @v_str_sql nvarchar(500),@list_id varchar(50)
	SET NOCOUNT ON
	If @p_staff_id_list <> ''
		Begin
			-- Tao bang trung gian
			Create Table #T_STAFF(FK_STAFF Varchar(50))
			Exec Sp_ListToTable @p_staff_id_list, 'FK_STAFF', '#T_STAFF', @psDelimitor	
			-- Lay quyen cua NSD
			Select PK_PERMISSION,FK_STAFF,C_PERMISSION_LIST From T_EFYLIB_PERMISSTION Where FK_STAFF In (Select FK_STAFF From #T_STAFF)
		End
	Else
		Begin
			Select PK_PERMISSION,FK_STAFF,C_PERMISSION_LIST From T_EFYLIB_PERMISSTION
		End	
	SET NOCOUNT OFF
Return 0
Go
/*
Exec dbo.EfyLib_StaffPermissionGetAll
	@p_staff_id_list	= ''
	,@psDelimitor		= '!~~!'

Doc_StaffPermissionGetAll '2054!~~!2055!~~!2056!~~!2057!~~!2059'
*/
------------------------------------------------------------------------------------------------------------------------
--Nguoi tao: Vu Manh Hung
--Ngay tao: 16/12/2011
--Chuc nang: Cap nhat quyen NSD theo don vi tham gia BHXH
CREATE PROCEDURE dbo.[EfyLib_UnitInsuranceStaffPermissionUpdate]
	@pStaffIdList				Nvarchar(max) = ''					-- Danh sach ID NSD
	,@pUnitInsuranceList		Nvarchar(max) = ''					-- Danh sach don vi
	,@pDistrictCodeList			Nvarchar(4000) = ''					-- Danh sach ma quan/huyen
	,@psDelimitor				Varchar(10)							-- Ky tu phan tach giua cac phan tu
	,@psHandleStatus			Varchar(30)	= 'THULY_THEO_DONVI'	-- Trang thai thu ly ho so (THULY_THEO_DONVI/THULY_THEO_DIABAN)
WITH ENCRYPTION --Ma hoa SP
AS
	Declare @Index Int, @count Int, @countStaff Int, @p_new_id Uniqueidentifier, @staffId Varchar(50)
	SET XACT_ABORT ON
	BEGIN TRANSACTION
	Set @count = dbo.f_ListLen(@pStaffIdList,@psDelimitor)	
		Set @Index = 1
		While @Index <= @count
		Begin
			-- Id staff
			Set @staffId = dbo.f_ListGetAt(@pStaffIdList,@Index,@psDelimitor)
			--Xoa NSD dang xet di
			delete from T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE Where FK_STAFF = @staffId
			--Insert lai du lieu			
			Set @p_new_id = newid()
			Insert Into T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE (PK_PERMISSION_HANDLE_UNIT_INSURANCE,FK_STAFF,C_UNIT_INSURANCE_CODE_LIST,C_DISTRICT_CODE_LIST,C_HANDLE_STATUS) Values (@p_new_id,@staffId,@pUnitInsuranceList,@pDistrictCodeList,@psHandleStatus)			
			Set @Index = @Index + 1
		End			
	COMMIT TRANSACTION 	
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec [EfyLib_UnitInsuranceStaffPermissionUpdate] 
'5304267D-0F38-43D3-AD36-C5DCB928FA8A!~~!F89E2A44-C241-471C-B980-132EB5EA4A5C!~~!21D2DE4E-8C96-47F6-85EB-7DC2727BB576'
,'','!~~!'

Exec dbo.[EfyLib_UnitInsuranceStaffPermissionUpdate]
	@pStaffIdList			= 'A7D84EEB-D08C-4C42-AA1D-D8573936B0EA,6A2C7F9F-5DE9-4D50-BABB-BF7639620581,4B148225-F3C2-4A26-A438-67C19BB9DA9E,8257BF1C-93EE-4977-85CB-126051FA573E,FE0114EE-26BB-46D3-B4C5-7CDB4D5547C8,56BD88A0-4789-4914-BCDF-EDAB8527AFB2,1F27BD96-A7A5-4E90-8DBE-4E2D3870C309,CD2ACC81-DCAF-4438-AD5E-4B958AB2500B,35B777DA-185F-4985-ACEC-412DE5F037C2,CBC510E8-0A30-462D-B440-436D1AB84979,D01A3F96-1016-4724-9E31-11861260CDD9,D56F3016-1234-43EF-8D6F-0042894DF62D,068CCE65-57F9-437C-8162-2B063F29B546,F33D824F-8A6C-4DA4-BAF8-020808821E1B,CC5E9819-CECE-45C2-B354-8FAA309FAB9F,AC9E47A0-1101-4DEA-9DCA-DD6916380EDF,7EBFD136-25F7-4144-80E0-DDFB15F692F6,8BC369CE-434B-4C4F-BC91-518EB9A6262D,74C5ED8D-43EB-4228-B8C7-86B2579D4F46,4203C555-1D57-4EE7-8FF7-D465BD5EE512,CC21DE29-9014-4C37-86D7-5F20D728A4A9,BB44C51A-8D30-42DD-86E5-6AAAABFE66AB,4C051332-CB2D-4F4F-BFFF-A4BCAC014195,35DADA32-10FC-43C0-B700-3A0889A4C74F,2EB0DFEA-5973-495A-8D22-EDF9F484F14C,381BE723-D4B1-4FB5-9221-328CE7252252'
	,@pUnitInsuranceList	= '410001A,BA13107,BA13355,BA13580,BA13853,BA14116,BC0001A,BG0001A,BG0002A,BG0003A,BG0004A,BH0001A,BH0002A,BH0003A,BH0004A,BH0005A,BH0006A,BH0007A,BH0008A,BH0009A,BH0010A,BH0011A,BH0012A,BH0013A,BH0014A,BH0015A,BQ0001A,BQ0002A,BS0001A,BS0002A,BS0003A,BS0004A,BS0005A,BS0006A,BS0007A,BS07384,BS09383,BS11037,BS11038,BS11040,BS11041,BS11042,BS11043,BS11044,BS11073,BS11082,BS11087,BS11089'
	,@psDelimitor			= ','
delete from T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE
Select * From T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE
*/
----------------------------------------------------------
--Nguoi tao: Vu Manh Hung
--Ngay tao: 16/12/2011
--Chuc nang: Lay quyen NSD theo don vi tham gia BHXH
CREATE PROCEDURE dbo.[EfyLib_UnitInsuranceStaffPermissionGetAll]
	@sDBLink						Varchar(50)						--Ten Link server
	,@sUnitId						Varchar(50)						--Id phong ban
	,@sStatus						Varchar(20)						--Trang thai hoat dong
	,@sFullTextSearch				NVarchar(50)					--FullTextSearch
	,@sOwnerCode					NVarchar(20)					--Ma don vi su dung
WITH ENCRYPTION --Ma hoa SP
AS
	Declare @Sql Varchar(2000)
	SET NOCOUNT ON
	Set @Sql = 'Select 
		B.C_NAME
		,A.FK_STAFF
		,C.C_CODE As C_POSITION_CODE
		,D.C_NAME As C_UNIT_NAME
		,A.C_UNIT_INSURANCE_CODE_LIST
	From T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE A Inner Join DBLink.[efy-user-bhhanoi].dbo.T_USER_STAFF B On A.FK_STAFF = B.PK_STAFF
	Inner Join ' + @sDBLink + +'.dbo.T_USER_POSITION C On B.FK_POSITION = C.PK_POSITION
	Inner Join ' + @sDBLink + '.dbo.T_USER_UNIT D On B.FK_UNIT = D.PK_UNIT Where B.C_STATUS = '  + Char(39) + @sStatus + Char(39)
	--Tim theo phong ban
	If @sUnitId <> ''
		Begin
			Set @Sql = @Sql + ' And B.FK_UNIT = ' + Char(39) + @sUnitId + Char(39)
		End
	--Tim theo Fulltextsearch
	If @sFullTextSearch <> ''
		Begin
			Set @Sql = @Sql + ' And B.C_NAME Like ' + Char(39) +'%' + @sFullTextSearch + '%' + Char(39)
		End		
	--Tim theo don vi su dung
	If @sOwnerCode <> ''
		Begin
			Set @Sql = @Sql + ' And D.C_OWNER_CODE Like ' + Char(39) + @sOwnerCode + Char(39)
		End				
	--Print @Sql
	Set @Sql = @Sql + ' Order by B.FK_UNIT, B.C_NAME'
	Exec(@Sql)
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec dbo.[EfyLib_UnitInsuranceStaffPermissionGetAll] 'DBLink.[efy-user-bhhanoi]','','HOAT_DONG','','BHXHHN'
Select * From T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE
select * from DBLink.[efy-user-bhhanoi].dbo.T_USER_STAFF
*/
--*/
-----------------------------------------------------------------------
--Nguoi tao: Vu Manh Hung
--Ngay tao: 18/12/2011
--Chuc nang: Lay don vi NSD da duoc ban quyen
CREATE PROCEDURE dbo.[EfyLib_UnitInsuranceGetAll]
	@sStaffIdList					NVarchar(4000)						--Danh sach ID can bo
	,@psDelimitor					Varchar(10)							-- Ky tu phan tach giua cac phan tu
	,@sDBLink						Varchar(50)	= ''					--Ten Link server
WITH ENCRYPTION --Ma hoa SP
AS
	Declare @Sql Varchar(2000), @Index Int, @iCountStaff Int, @sStaffId Varchar(50), @sUnitInsuranceCodeList NVarchar(4000)
	Declare 
		@UnitInsuranceTabTemp TABLE (FK_STAFF [Varchar] (50), C_UNIT_INSURANCE_CODE_LIST [nvarchar] (4000))
	Declare 
		@UnitInsuranceTab TABLE (FK_STAFF [Varchar] (50), C_UNIT_INSURANCE_CODE [nvarchar] (40))
	SET NOCOUNT ON
	--Neu NSD truyen vao danh sach ID NSD
	If @sStaffIdList <> ''
		Begin
			--Insert danh sach quyen cua NSD thoan ma @sStaffIdList can bo truyen vao
			Insert Into @UnitInsuranceTabTemp(FK_STAFF, C_UNIT_INSURANCE_CODE_LIST)
			Select A.[part],B.C_UNIT_INSURANCE_CODE_LIST From [dbo].[f_ConvertStringToTable](@sStaffIdList,@psDelimitor) A
			Inner Join T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE B On A.[part] = B.FK_STAFF
			Select @iCountStaff = Count(FK_STAFF) From @UnitInsuranceTabTemp
			Set @Index = 1
			While @Index <= @iCountStaff
				Begin
					Select @sStaffId = dbo.f_ListGetAt(@sStaffIdList, @Index, @psDelimitor)
					Select @sUnitInsuranceCodeList = C_UNIT_INSURANCE_CODE_LIST From @UnitInsuranceTabTemp Where FK_STAFF = @sStaffId
					Insert Into @UnitInsuranceTab (FK_STAFF, C_UNIT_INSURANCE_CODE)
					Select @sStaffId,[part] From [dbo].[f_ConvertStringToTable](@sUnitInsuranceCodeList,@psDelimitor)
					Set @Index = @Index + 1
				End
		End 	
	--Hien thi du lieu
	Select Distinct A.C_CODE, A.C_NAME From T_EFYLIB_LIST A 
	Inner Join T_EFYLIB_LISTTYPE B On A.FK_LISTTYPE = B.PK_LISTTYPE
	Inner Join @UnitInsuranceTab C On C.C_UNIT_INSURANCE_CODE = A.C_CODE
	Where B.C_CODE = 'DM_DON_VI_THAM_GIA_BHXH'	
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec [EfyLib_UnitInsuranceGetAll] '{4B148225-F3C2-4A26-A438-67C19BB9DA9E}','!~~!'
Exec dbo.[EfyLib_UnitInsuranceGetAll] '6F359421-8E45-400C-A477-A630857FE216!~~!5304267D-0F38-43D3-AD36-C5DCB928FA8A!~~!05B17CBD-2463-4153-AFD7-90CC3D423D8D!~~!B26BDD65-DC1C-4EC1-AB2B-8A99280174F3','!~~!'
Select * From T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE
*/
-----------------------------------------------------------------------
--Nguoi tao: Vu Manh Hung
--Ngay tao: 18/12/2011
--Chuc nang: Lay dia ban quan/huyen da duoc ban quyen
CREATE PROCEDURE dbo.[EfyLib_PermissiondDistrictGetAll]
	@sStaffIdList					NVarchar(4000)						--Danh sach ID can bo
	,@psDelimitor					Varchar(10)							-- Ky tu phan tach giua cac phan tu
WITH ENCRYPTION --Ma hoa SP
AS
	Declare @Sql Varchar(2000), @Index Int, @iCountStaff Int, @sStaffId Varchar(50), @sDistrictCodeList NVarchar(4000)
	Declare 
		@DistrictTabTemp TABLE (FK_STAFF [Varchar] (50), C_DISTRICT_CODE_LIST [nvarchar] (4000))
	Declare 
		@DistrictTab TABLE (FK_STAFF [Varchar] (50), C_DISTRICT_CODE [NVarchar] (30))
	SET NOCOUNT ON
	--Neu NSD truyen vao danh sach ID NSD
	If @sStaffIdList <> ''
		Begin
			--Insert danh sach quyen cua NSD thoan ma @sStaffIdList can bo truyen vao
			Insert Into @DistrictTabTemp(FK_STAFF, C_DISTRICT_CODE_LIST)
			Select A.[part],B.C_DISTRICT_CODE_LIST From [dbo].[f_ConvertStringToTable](@sStaffIdList,@psDelimitor) A
			Inner Join T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE B On A.[part] = B.FK_STAFF
			Select @iCountStaff = Count(FK_STAFF) From @DistrictTabTemp
			Set @Index = 1
			While @Index <= @iCountStaff
				Begin
					Select @sStaffId = dbo.f_ListGetAt(@sStaffIdList, @Index, @psDelimitor)
					Select @sDistrictCodeList = C_DISTRICT_CODE_LIST From @DistrictTabTemp Where FK_STAFF = @sStaffId
					Insert Into @DistrictTab (FK_STAFF, C_DISTRICT_CODE)
					Select @sStaffId,[part] From [dbo].[f_ConvertStringToTable](@sDistrictCodeList,@psDelimitor)
					Set @Index = @Index + 1
				End
		End 	
	--Hien thi du lieu
	Select Distinct A.C_CODE, A.C_NAME From T_EFYLIB_LIST A 
	Inner Join T_EFYLIB_LISTTYPE B On A.FK_LISTTYPE = B.PK_LISTTYPE
	Inner Join @DistrictTab C On C.C_DISTRICT_CODE = A.C_CODE
	Where B.C_CODE = 'DM_QUAN_HUYEN'	
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec [EfyLib_PermissiondDistrictGetAll] '97EC2F16-9E4A-425B-B9C9-E61BD3F7C169','!~~!'
Select * From T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE
*/
-----------------------------------------------------------------------
--Nguoi tao: Vu Manh Hung
--Ngay tao: 18/12/2011
--Chuc nang: Lay don vi NSD da duoc ban quyen phuc vu xuat du lieu ra XML
CREATE PROCEDURE dbo.[EfyLib_UnitInsuranceGetAllForGenerateXmlData]
	@sDBLink						Varchar(50)	= ''					--Ten Link server
WITH ENCRYPTION --Ma hoa SP
AS	
	Declare @Sql Varchar(2000)
	SET NOCOUNT ON
	Set @Sql = 'Select A.FK_STAFF, C.C_CODE, B.C_NAME, D.C_NAME As C_UNIT_NAME, A.C_UNIT_INSURANCE_CODE_LIST
				From T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE A Inner Join ' + @sDBLink + '.dbo.T_USER_STAFF B On A.FK_STAFF = B.PK_STAFF
				Inner Join ' + @sDBLink + '.dbo.T_USER_POSITION C On C.PK_POSITION = B.FK_POSITION
				Inner Join ' + @sDBLink + '.dbo.T_USER_UNIT D On D.PK_UNIT = B.FK_UNIT'
	Exec(@Sql)			
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec [EfyLib_UnitInsuranceGetAllForGenerateXmlData] 'DBLink.[efy-user-bhhanoi]'
Exec dbo.[EfyLib_UnitInsuranceGetAll] '6F359421-8E45-400C-A477-A630857FE216!~~!5304267D-0F38-43D3-AD36-C5DCB928FA8A!~~!05B17CBD-2463-4153-AFD7-90CC3D423D8D!~~!B26BDD65-DC1C-4EC1-AB2B-8A99280174F3','!~~!'
Select * From T_eCS_PERMISSION_HANDLE_UNIT_INSURANCE
*/
